#!/usr/bin/env python
# -*- coding: utf-8 -*-

from utils import log
from sqlalchemy.orm import mapper, sessionmaker
from datetime import datetime
from sqlalchemy import Table, MetaData, Column, ForeignKey, Integer, BigInteger, String, Unicode, UnicodeText, DateTime
from controls.data_pool_mysql import engine

# 创建到数据库的连接,echo=True表示用logging输出调试结果
metadata = MetaData()
Session = sessionmaker()  # 创建了一个自定义了的 Session类
Session.configure(bind=engine)  # 将创建的数据库连接关联到这个session

"""user_base collection has 
   userid:     用户id (int)
   phone:      绑定手机号 (str)
   passwd:     密码，密文 (str)
   ctime:      注册时间(datetime)
"""


class UserBaseItem(object):  # 创建一个映射类

    def to_dict(self):
        res = {}
        res['userid'] = self.userid
        res['phone'] = self.phone
        res['passwd'] = self.passwd
        res['ctime'] = self.ctime
        return res

    def __repr__(self):
        return "%d" % (self.userid)


user_base_table = Table(
    'user_base', metadata,
    Column('id', Integer, primary_key=True),
    Column('userid', Integer, index=True, unique=True, nullable=False),
    Column('phone', Unicode(65), index=True, default=''),
    Column('passwd', Unicode(65), nullable=True),
    Column('ctime', DateTime, default=datetime.now, nullable=False),
)

mapper(UserBaseItem, user_base_table)  # 把表映射到类


def create_user_base_table_if_not_exist():
    metadata.create_all(engine)  # 在数据库中生成表
    # mapper( UserBaseItem, user_base_table ) #把表映射到类


def add_user_base_item(p_item):
    item = UserBaseItem()
    item.userid = p_item['userid']
    item.phone = p_item['phone']
    item.passwd = p_item['passwd']
    try:
        session = Session()
        session.add(item)  # 往session中添加内容
        session.flush()  # 保存数据
        session.commit()  # 数据库事务的提交,sisson自动过期而不需要关闭
        return True
    except Exception as e:
        log.exp(e)
        session.rollback()
        return False
    finally:
        session.close()
        # session.remove()


def add_empty_user_base_item(userid):
    item = UserBaseItem()
    item.userid = userid
    try:
        session = Session()
        session.add(item)  # 往session中添加内容
        session.flush()  # 保存数据
        session.commit()  # 数据库事务的提交,sisson自动过期而不需要关闭
        return True
    except Exception as e:
        log.exp(e)
        session.rollback()
        return False
    finally:
        session.close()


def update_passwd_by_phone(p_phone, p_passwd):
    try:
        session = Session()
        log.i('======p_phone=%s, p_passwd=%s=====' % (p_phone, p_passwd))
        item = session.query(UserBaseItem).filter(UserBaseItem.phone == p_phone).update({UserBaseItem.passwd: p_passwd},
                                                                                 synchronize_session=False)
        log.i('======item=%s======' % item)
        session.flush()
        session.commit()  # 提交事务
        return True
    except Exception as e:
        log.exp(e)
        session.rollback()
        return False
    finally:
        session.close()
        # session.remove()


def update_user_phone_and_password_by_userid(p_userid, p_phone, p_passwd):
    try:
        session = Session()
        session.query(UserBaseItem).filter(UserBaseItem.userid == p_userid)\
            .update({UserBaseItem.phone: p_phone,
                     UserBaseItem.passwd: p_passwd},
                    synchronize_session=False)
        session.commit()  # 提交事务
        return True
    except Exception as e:
        log.exp(e)
        session.rollback()
        return False
    finally:
        session.close()
        # session.remove()


def get_user_base_by_userid(p_userid):
    try:
        session = Session()
        item = session.query(UserBaseItem).filter_by(userid=p_userid).first()
        if not item:
            return None
        res = {
            'userid': item.userid,
            'phone': item.phone,
            'passwd': item.passwd,
            'ctime': item.ctime,
        }
        return res
    except Exception as e:
        log.exp(e)
        return None
    finally:
        session.close()
        # session.remove()

def get_user_base_ctime_by_userid(p_userid):
    try:
        session = Session()
        item = session.query(UserBaseItem).filter_by(userid=p_userid).first()
        res = item.ctime if item.ctime else None
        return res
    except Exception as e:
        log.exp(e)
        return None
    finally:
        session.close()

def get_user_base_by_phone(p_phone):
    try:
        session = Session()
        item = session.query(UserBaseItem).filter_by(phone=p_phone).first()
        if not item:
            return None
        return item.to_dict()
    except Exception as e:
        log.exp(e)
        return None
    finally:
        session.close()


if __name__ == "__main__":
    create_user_base_table_if_not_exist()
    item1 = {u'userid': 1234, u'email': u'yexiang@shitouren.com', u'phone': u'', u'passwd': u'hahahaha'}
    item2 = {u'userid': 5678, u'email': u'yangmin@shitouren.com', u'phone': u'', u'passwd': u'hahahaha'}
    add_user_base_item(item1)
    add_user_base_item(item2)
